package com.erp.freight.service.impl;

import cn.hutool.core.bean.BeanUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.erp.freight.domain.*;
import com.erp.freight.domain.bo.ErpProductFreightBo;
import com.erp.freight.domain.vo.ErpProductFreightVo;
import com.erp.freight.mapper.*;
import com.erp.freight.service.IErpProductFreightService;
import com.erp.freight.service.IErpUpdateFreightTemplateService;
import com.ruoyi.common.core.domain.PageQuery;
import com.ruoyi.common.core.excel.ExcelReader;
import com.ruoyi.common.core.exception.ServiceException;
import com.ruoyi.common.core.utils.StringUtils;
import com.ruoyi.common.core.web.oldPage.TableDataInfo;
import com.ruoyi.common.security.utils.DictUtils;
import lombok.RequiredArgsConstructor;
import org.apache.poi.util.StringUtil;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.io.IOException;
import java.math.BigDecimal;
import java.util.*;

/**
 * 更新运费模版（各物流公司运费格读取）Service业务层处理
 *
 * @author zj
 * @date 2023-06-23
 */
@RequiredArgsConstructor
@Service
public class ErpUpdateFreightTemplateServiceImpl implements IErpUpdateFreightTemplateService {

    private final ErpProductFreightMapper baseMapper;

    private final ErpProductFreightBaleMapper baleMapper;

    private final ErpProductFreightYanwenMapper yanwenMapper;

    private final ErpProductFreightCountryZoneMapper countryZoneMapper;

    private final ErpProductFreightEconomyMapper economyMapper;

    @Override
    @Transactional
    public Boolean updateFreightTrialTemplate(String filePath, String type) {
        try {
            ExcelReader reader = new ExcelReader(filePath);
            Map<String, Map<String, String>> fileMap = reader.readAllSheets();

            if(type.trim().equals("CAINIAO")){
                getCainiaoTemplate(fileMap);
            }else if(type.trim().equals("YANWEN")){
                getYanwenTemplate(fileMap);
            }

            System.out.println("读取完成");

        } catch (IOException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    /**
     * 读取菜鸟运费模版
     * @param fileMap
     * @return
     */
    private Boolean getCainiaoTemplate(Map<String, Map<String, String>> fileMap){
        String reversion = baseMapper.selectMaxReversionByFreightType("菜鸟无忧物流-标准");

        // 获取菜鸟无忧物流-标准
//        Map<String, String> cnbzSheetMap = fileMap.get("菜鸟无忧物流-标准");
//        updateCainiaoBiaoZhun(cnbzSheetMap, reversion, "菜鸟无忧物流-标准");

        // 获取菜鸟大包专线
//		Map<String, String> cndbzxSheetMap = fileMap.get("菜鸟大包专线");
//		updateCainiaoDaBaoZhuanXian(cndbzxSheetMap, reversion);
//
		// 获取菜鸟特货专线-标准
//		Map<String, String> cnthzxSheetMap = fileMap.get("菜鸟特货专线－标准");
//		updateCainiaoTeHuoZhuanXian(cnthzxSheetMap, reversion);

		// 获取菜鸟超级经济Global
		Map<String, String> cnjjSheetMap = fileMap.get("菜鸟超级经济Global");
//		updateCainiaoEconomy(cnjjSheetMap, reversion, "菜鸟超级经济Global");
//
		// 获取菜鸟专线经济
//		cnjjSheetMap = fileMap.get("菜鸟专线经济");
//		updateCainiaoEconomy(cnjjSheetMap, reversion, "菜鸟专线经济");

		// 获取菜鸟特货专线－超级经济
//		cnjjSheetMap = fileMap.get("菜鸟特货专线－超级经济");
//		updateCainiaoEconomy(cnjjSheetMap, reversion, "菜鸟特货专线－超级经济");

		// 获取菜鸟超级经济
//		cnjjSheetMap = fileMap.get("菜鸟超级经济");
//		updateCainiaoEconomy(cnjjSheetMap, reversion, "菜鸟超级经济");

		// 获取菜鸟无忧物流-简易
//		Map<String, String> cnjySheetMap = fileMap.get("菜鸟无忧物流-简易");
//		updateCainiaoBiaoZhun(cnjySheetMap, reversion, "菜鸟无忧物流-简易");


        return true;
    }

    /**
     * 读取燕文运费模版
     * @param fileMap
     * @return
     */
    private Boolean getYanwenTemplate(Map<String, Map<String, String>> fileMap){
        String reversion = baseMapper.selectYWMaxReversionByFreightType("燕文专线追踪-普货");

//        // 获取澳大利亚国家分区
        Map<String, String> sheetMap = fileMap.get("国家分组-澳洲邮编");
        updateCountryZone(sheetMap, reversion, "澳大利亚", "AU", "燕文");

        // 获取燕文专线追踪-普货
        sheetMap = fileMap.get("燕文专线追踪-普货");
        updateYanWenLogistics(sheetMap, reversion, "燕文专线追踪-普货");

        // 获取燕文专线追踪-特货
        sheetMap = fileMap.get("燕文专线追踪-特货");
//        updateCainiaoDaBaoZhuanXian(sheetMap, reversion);
        updateYanWenLogistics(sheetMap, reversion, "燕文专线追踪-特货");

        // 获取燕文专线快递-普货
        sheetMap = fileMap.get("燕文专线快递-普货");
//        updateCainiaoDaBaoZhuanXian(sheetMap, reversion);
        updateYanWenLogistics(sheetMap, reversion, "燕文专线快递-普货");

        // 获取燕文专线快递-特货
        sheetMap = fileMap.get("燕文专线快递-特货");
//        updateCainiaoDaBaoZhuanXian(sheetMap, reversion);
        updateYanWenLogistics(sheetMap, reversion, "燕文专线快递-特货");

        // 获取燕文专线惠选-普货
        sheetMap = fileMap.get("燕文专线惠选-普货");
//        updateCainiaoDaBaoZhuanXian(sheetMap, reversion);
        updateYanWenLogistics(sheetMap, reversion, "燕文专线惠选-普货");

        // 获取燕文化妆品专线
        sheetMap = fileMap.get("燕文化妆品专线");
//        updateCainiaoDaBaoZhuanXian(sheetMap, reversion);
        updateYanWenLogistics(sheetMap, reversion, "燕文化妆品专线");

        return true;
    }


    /**
     * 更新菜鸟无忧物流-标准运费（主要用作运费试算）
     * @param sheetMap
     * @return
     */
    private Boolean updateCainiaoBiaoZhun(Map<String, String> sheetMap, String reversion, String freightType){

        // 计费标准Map
        Map<String, String> feeMap = new HashMap<>();
        // 将计费标准单独拆分出来
        for (String key : sheetMap.keySet()) {
            if (sheetMap.get(key).contains("小包普货") || sheetMap.get(key).contains("小包非普货")
                    || sheetMap.get(key).contains("大包计费") || sheetMap.get(key).contains("(含)")
                    || sheetMap.get(key).contains("服务费") || sheetMap.get(key).contains("首重")
                    || sheetMap.get(key).contains("续重")) {
                feeMap.put(key, sheetMap.get(key));
            }
        }

        // 遍历计费标准
        for (String key : feeMap.keySet()) {
            // 第一个配送服务费
            if (key.contains(",4") && feeMap.get(key).contains("配送服务费")) {
                // 获取key，转换坐标，如(16,4)，并根据坐标获取具体数据
                insertProductFreightByMap(key, sheetMap, freightType,
                        (reversion == null || reversion.trim().equals("")) ? "1" : reversion);
            }
        }
        return true;
    }


    /**
     * 更新国家分区
     * @param sheetMap
     * @return
     */
    private Boolean updateCountryZone(Map<String, String> sheetMap, String reversion, String country,
                                      String countryCode, String logisticsCompany){

        List<ErpProductFreightCountryZone> countryZoneList = new ArrayList<>();
        // 邮编Map
        Map<String, String> zipCodeMap = new HashMap<>();
        // 将邮编明细这一列单独拆分出来
        for (String key : sheetMap.keySet()){
            if(sheetMap.get(key).contains("邮编明细")){
                zipCodeMap.put(key, sheetMap.get(key));
            }
        }
        ErpProductFreightCountryZone countryZone = null;
        // 遍历计费标准
        for (String key : zipCodeMap.keySet()){
            // 第一个配送服务费
            if(key.contains(",2") && zipCodeMap.get(key).contains("邮编明细")){
                // 获取key，转换坐标，如(8,2)，并根据坐标获取具体数据
                /**
                 * 根据邮编坐标（比如坐标是8,2），获取邮编的下一列
                 * 1、如果(9,2)是数字，则为有效行，否则退出
                 */
                // 根据坐标物，获取其他坐标
                String positionX = key.substring(0, key.indexOf(","));
                String positionY = key.substring(key.indexOf(",")+1);

                // X坐标遍历
                int posX = Integer.parseInt(positionX);
                // Y坐标遍历
                int posY = Integer.parseInt(positionY);
                // 新坐标
                String newCoordinate = "";

                loop1 : while (true){
                    posX += 1;
                    newCoordinate = "" + posX + "," + posY + "";
                    // 根据具体数字判断是否是有效行
                    if(StringUtils.isNumber(sheetMap.get(newCoordinate))){
                        countryZone = new ErpProductFreightCountryZone();
                        countryZone.setCountry(country);
                        countryZone.setCountryCode(countryCode);
                        countryZone.setRevision(reversion == null || reversion.trim().equals("") ? "1" : (Integer.parseInt(reversion) + 1) + "");
                        countryZone.setLogisticsCompany(logisticsCompany);
                        // 插入邮编
//                        countryZone.setZipCode(Integer.parseInt(sheetMap.get(newCoordinate)) + "");
                        countryZone.setZipCode(sheetMap.get(newCoordinate));
                        // 插入分区
                        countryZone.setZone(sheetMap.get("" + posX + "," + (posY-1) + ""));
                        countryZoneList.add(countryZone);
                    }else {
                        break loop1;
                    }
                }
            }
        }
        countryZoneMapper.insertBatch(countryZoneList);
        return true;
    }

    /**
     * 更新燕文物流
     * @param sheetMap
     * @return
     */
    private Boolean updateYanWenLogistics(Map<String, String> sheetMap, String reversion, String freightType){

        // 计费标准Map
        Map<String, String> feeMap = new HashMap<>();
        // 将国家这一列单独拆分出来
        for (String key : sheetMap.keySet()){
            if(sheetMap.get(key).trim().equals("国家")){
                feeMap.put(key, sheetMap.get(key));
            }
        }

        // 遍历计费标准
        for (String key : feeMap.keySet()){
            String x = key.split(",")[0];
            String y = key.split(",")[1];
            String numberKey = (Integer.parseInt(x)+1) + "," + (Integer.parseInt(y)+3);
            // 第一个配送服务费
            if(key.contains(",2") && feeMap.get(key).contains("国家") && StringUtils.isNumber(sheetMap.get(numberKey))){
                // 获取key，转换坐标，如(16,4)，并根据坐标获取具体数据
                insertYanWenProductFreightByMap(key, sheetMap, freightType, (reversion == null || reversion.trim().equals("")) ? "1" : reversion);
            }
        }
        return true;
    }


    /**
     * 根据读取的Excel信息，将运费插入数据库
     *
     * @param coordinate
     * @param sheetMap
     * @param freightType
     * @param version
     * @return
     */
    private int insertProductFreightByMap(String coordinate, Map<String, String> sheetMap, String freightType,
                                          String version) {
        ErpProductFreight erpProductFreight = null;
        List<ErpProductFreight> erpProductFreightList = new ArrayList<>();

        ErpProductFreightBale erpProductFreightBale = null;
        List<ErpProductFreightBale> erpProductFreightBaleList = new ArrayList<>();

        // 判断费用币种类型
        boolean isRMB = true;
        if(sheetMap.get(coordinate).contains("美元") || sheetMap.get(coordinate).contains("USD")){
            isRMB = false;
        }

        /**
         * 根据配送服务费，处理坐标，并获取数据坐标，如(15,4)
         * 1、数据坐标是(16,1),(16,2),(16,3)...(17,1),(17,2),(17,3)...直至为空
         * 2、重量范围坐标为(14,4)
         * 3、包裹类型坐标为(13,4)
         */
        // 根据坐标物，获取其他坐标
        String positionY = coordinate.substring(0, coordinate.indexOf(","));

        // Y坐标遍历
        int posY = Integer.parseInt(positionY);
        // 新坐标
        String newCoordinate = "";
        // 自增标记
        int ii = 0;
        loop1: while (true) {
            posY += 1;
            newCoordinate = "" + posY + ",4" + "";
            // 根据具体数字判断是否是有效行
            if (StringUtils.isNumber(sheetMap.get(newCoordinate))) {

                // 判断是否为有效列
                loop2: for (int j = 4; j < 20; j++) {
                    if (StringUtils.isNumber(sheetMap.get(posY + "," + j))) {
                        // 有效行
                        erpProductFreight = new ErpProductFreight();
                        // 赋值国家基本信息
                        erpProductFreight.setFreightType(freightType);
                        erpProductFreight.setCountry(sheetMap.get(posY + ",1"));
                        erpProductFreight.setCountryEn(sheetMap.get(posY + ",2"));
                        erpProductFreight.setCountryCode(sheetMap.get(posY + ",3"));
                        // 升级物流模版版本
                        erpProductFreight.setRevision((Integer.parseInt(version) + 1) + "");

                        // 单元格为数字，获取起重重量
                        String startWeight = sheetMap.get(Integer.parseInt(positionY) - 1 + "," + j);
                        String packageType = sheetMap.get(Integer.parseInt(positionY) - 2 + "," + j);
                        if (((startWeight.contains("~") || startWeight.contains("-") || startWeight.contains("g以内"))
                                && startWeight.contains("g")) || startWeight.contains("KG以内")) {
                            // 小包需要完全匹配，才能获取重量
                            if(startWeight.contains("~")){
                                erpProductFreight.setWeight(
                                        startWeight.substring(startWeight.indexOf("~") + 1, startWeight.indexOf("g")));
                            }else if(startWeight.contains("-")){
                                erpProductFreight.setWeight(
                                        startWeight.substring(startWeight.indexOf("-") + 1, startWeight.indexOf("g")));
                            }else if(startWeight.contains("g以内")){
                                erpProductFreight.setWeight(
                                        startWeight.substring(0, startWeight.indexOf("g以内")));
                            }else if(startWeight.contains("KG以内")){
                                erpProductFreight.setWeight(
                                        new BigDecimal(startWeight.substring(0, startWeight.indexOf("KG以内")))
                                                .multiply(new BigDecimal("1000")).toString());
                            }else {
                                throw new RuntimeException();
                            }

                            // 配送服务费
                            BigDecimal freight = new BigDecimal(sheetMap.get(posY + "," + j));
                            erpProductFreight.setFreight(isRMB ? freight : freight.multiply(new BigDecimal("7.2")));
                            // 挂号费
                            j++;
                            BigDecimal registrationFee = new BigDecimal(sheetMap.get(posY + "," + j));
                            erpProductFreight.setRegistrationFee(isRMB ? registrationFee : registrationFee.multiply(new BigDecimal("7.2")));

                            // 包裹类型，存在普货和非普货同样价格的问题
                            if(packageType == null || (packageType.contains("小包非普货") && packageType.contains("小包普货"))){
                                // 小包普货和非普货同价，各加一条记录
                                erpProductFreight.setPackageType(0);
                                erpProductFreightList.add(erpProductFreight);

                                ErpProductFreight erpProductFreight2 = new ErpProductFreight();
                                BeanUtils.copyProperties(erpProductFreight, erpProductFreight2);
                                erpProductFreight2.setPackageType(1);
                                erpProductFreightList.add(erpProductFreight2);
                            }else {
                                erpProductFreight.setPackageType(packageType.contains("非普货") ? 1 : 0);
                                erpProductFreightList.add(erpProductFreight);
                            }
                        }else if(startWeight.contains("限重") || startWeight.contains("大包")){
                            /**
                             * 大包需要完全匹配
                             * 需要判断是否计泡
                             * 两种计费方式：按g计重和首重、续重
                             */
                            // 有效行
                            erpProductFreightBale = new ErpProductFreightBale();
                            // 赋值国家基本信息
                            erpProductFreightBale.setFreightType(freightType);
                            erpProductFreightBale.setCountry(sheetMap.get(posY + ",1"));
                            erpProductFreightBale.setCountryEn(sheetMap.get(posY + ",2"));
                            erpProductFreightBale.setCountryCode(sheetMap.get(posY + ",3"));

                            // 升级物流模版版本
                            erpProductFreightBale.setRevision((Integer.parseInt(version) + 1) + "");

                            // 是否计泡
                            erpProductFreightBale.setBubbleType(packageType.contains("计泡") ? 1 : 0);
                            // 获取限重 没有默认20kg
                            String limitWeight = startWeight.contains("限重") ? startWeight : "20000";
                            // 获取限重单位
                            if(limitWeight.contains("kg")){
                                limitWeight = limitWeight.substring(limitWeight.indexOf("限重")+2, limitWeight.indexOf("kg")) + "000";
                            }
                            erpProductFreightBale.setLimitWeight(limitWeight);

                            // 大包
                            erpProductFreightBale.setPackageType(2);

                            // 获取首重
                            if(sheetMap.get(positionY + "," + j).contains("/KG")){
                                // 按g计重，获取1kg价格
                                erpProductFreightBale.setFirstWeight("1000");
                                erpProductFreightBale.setFirstFreight(new BigDecimal(sheetMap.get(posY + "," + j)));
                            }else if(sheetMap.get(positionY + "," + j).contains("首重")){
                                // 首重计算方式，则需要获取首重克数
                                String firstWeight = sheetMap.get(positionY + "," + j);
                                // 如果是KG，则需要乘以1000
                                if(sheetMap.get(positionY + "," + j).contains("KG")){
                                    erpProductFreightBale.setFirstWeight((new BigDecimal(firstWeight.substring(0, firstWeight.indexOf("KG")))
                                            .multiply(new BigDecimal("1000"))).toString());
                                    erpProductFreightBale.setFirstFreight(new BigDecimal(sheetMap.get(posY + "," + j)));
                                }else {
                                    // TODO 如果是G，目前没有发现，暂时不开发
                                }
                            }

                            j++;

                            // 获取续重或者服务费
                            if(sheetMap.get(positionY + "," + j).contains("续重")) {
                                // 下一列是续重
                                String firstWeight = sheetMap.get(positionY + "," + j);
                                // 如果是G，直接取值
                                erpProductFreightBale.setContinuationWeight(firstWeight.substring(firstWeight.indexOf("每") + 1, firstWeight.indexOf("G")));
                                erpProductFreightBale.setContinuationFreight(new BigDecimal(sheetMap.get(posY + "," + j)));

                                // TODO 续重如果是KG，目前没有发现，暂时不开发

                                // 判断下一列是否是挂号
                                j++;
                                if(sheetMap.containsKey(positionY + "," + j) && sheetMap.get(positionY + "," + j).contains("挂号")){
                                    // 下一列是挂号服务费
                                    erpProductFreightBale.setRegistrationFee(new BigDecimal(sheetMap.get(posY + "," + j)));
                                }
                            } else if(sheetMap.get(positionY + "," + j).contains("挂号")){
                                // 下一列是挂号服务费
                                erpProductFreightBale.setRegistrationFee(new BigDecimal(sheetMap.get(posY + "," + j)));
                                // 没有续重
                                erpProductFreightBale.setContinuationWeight("0");
                                erpProductFreightBale.setContinuationFreight(new BigDecimal("0"));
                            }

//                            break loop2;

                            erpProductFreightBaleList.add(erpProductFreightBale);
                        }else {
                            // 抛异常，停止导入
                            throw new ServiceException("数据读取异常，坐标为："+ (Integer.parseInt(positionY)-1) + "," + j + "，坐标值为：" + startWeight);
                        }

                    }
                }
                ii++;
            }else {
                // 不是有效行，直接跳出
                break loop1;
            }

        }
        baleMapper.insertBatch(erpProductFreightBaleList);
        return baseMapper.insertBatch(erpProductFreightList) ? ii : 0;
    }

    /**
     * 根据读取的Excel信息，将运费插入燕文物流专属的数据库表
     * @param coordinate
     * @param sheetMap
     * @param freightType
     * @param version
     * @return
     */
    private int insertYanWenProductFreightByMap(String coordinate, Map<String, String> sheetMap, String freightType, String version){
        ErpProductFreightYanwen erpProductFreightYanwen = null;
        List<ErpProductFreightYanwen> erpProductFreightYanwenList = new ArrayList<>();

        /**
         * 根据国家坐标（比如坐标是8,2），获取国家的后一列(8,4)
         * 1、如果后两列(8,4)为公斤运费，则为按g计重，否则为按续重计费
         * 2、如果按g计重，则获取国家的后一列(8,3)
         * 3、如果后一列(8,3)为CountryCode，则可直接获取国家代码，如果是分区，则为澳大利亚
         */
        // 根据坐标物，获取其他坐标
        String positionX = coordinate.substring(0, coordinate.indexOf(","));
        String positionY = coordinate.substring(coordinate.indexOf(",")+1);

        // X坐标遍历
        int posX = Integer.parseInt(positionX);
        // Y坐标遍历
        int posY = Integer.parseInt(positionY);
        // 新坐标
        String newCoordinate = "";
        // 自增标记
        int ii = 0;

        // 根据后两列判断计重方案
        newCoordinate = "" + posX + "," + (posY+2) + "";
        // 计重方案
        String weightCalculationScheme = "G";

        if(sheetMap.get(newCoordinate).contains("公斤运费")){
            // 获取国家的后一列
            newCoordinate = "" + posX + "," + (posY+1) + "";
            if(sheetMap.get(newCoordinate).contains("分区")){
                // 按区计费
                weightCalculationScheme = "ZONE";
            }
        }else if(sheetMap.get(newCoordinate).contains("重量段")){
            // 分段计费
            weightCalculationScheme = "SECTION";
        }else {
            throw new RuntimeException();
        }

        loop1 : while (true){
            posX += 1;
            if(weightCalculationScheme.equals("G")){
                int j = 4;
                newCoordinate = "" + posX + "," + j + "";
                // 根据具体数字判断是否是有效行
                if(StringUtils.isNumber(sheetMap.get(newCoordinate))) {
                    // 有效行
                    erpProductFreightYanwen = new ErpProductFreightYanwen();
                    // 赋值国家基本信息
                    erpProductFreightYanwen.setFreightType(freightType);
                    erpProductFreightYanwen.setCountry(sheetMap.get(posX + ",2"));
                    erpProductFreightYanwen.setCountryCode(sheetMap.get(posX + ",3"));
                    // 升级物流模版版本
                    erpProductFreightYanwen.setRevision((Integer.parseInt(version) + 1) + "");

                    // 获取公斤运费(元/KG)、处理费(元/件)、重量段(KG)、最小计费重量(KG)
                    // 获取公斤运费(元/KG)
                    String kgFreight = sheetMap.get(posX + "," + j);
//                    BigDecimal gFreight = new BigDecimal(kgFreight).divide(new BigDecimal("1000"));
                    // 获取处理费(元/件)
                    j++;
                    BigDecimal registrationFee = new BigDecimal(sheetMap.get(posX + "," + j));
                    // 获取重量段(KG)0.001 - 2
                    j++;
                    String weightSection = sheetMap.get(posX + "," + j);
                    String endWeightKg = weightSection.substring(weightSection.indexOf("-") + 1).trim();
                    String endWeightG = new BigDecimal(endWeightKg).multiply(new BigDecimal("1000")).toString();
                    String startWeightKg = weightSection.substring(0, weightSection.indexOf("-")).trim();
                    // 获取最小计费重量(KG)
                    j++;
                    String minimumWeightKg = sheetMap.get(posX + "," + j);
                    if (new BigDecimal(startWeightKg).compareTo(new BigDecimal(minimumWeightKg)) < 0) {
                        // 首重，可为空
                        erpProductFreightYanwen.setFirstWeight(new BigDecimal(minimumWeightKg).multiply(new BigDecimal("1000")).toString());
                    }
                    erpProductFreightYanwen.setWeight(endWeightG);
                    erpProductFreightYanwen.setFreight(new BigDecimal(kgFreight));
                    erpProductFreightYanwen.setRegistrationFee(registrationFee);
                    // 货品属性
                    erpProductFreightYanwen.setPackageType(0);

                    erpProductFreightYanwenList.add(erpProductFreightYanwen);
                    ii++;
                }else {
                    // 不是有效行，直接跳出
                    break loop1;
                }
            }else if(weightCalculationScheme.equals("SECTION")){
                int j = 4;
                newCoordinate = "" + (posX) + "," + (j+1) + "";
                // 根据具体数字判断是否是有效行
                if(StringUtils.isNumber(sheetMap.get(newCoordinate))) {
                    // 有效行
                    erpProductFreightYanwen = new ErpProductFreightYanwen();
                    // 赋值国家基本信息
                    erpProductFreightYanwen.setFreightType(freightType);
                    erpProductFreightYanwen.setCountry(sheetMap.get(posX + ",2"));
                    erpProductFreightYanwen.setCountryCode(sheetMap.get(posX + ",3"));
                    // 升级物流模版版本
                    erpProductFreightYanwen.setRevision((Integer.parseInt(version) + 1) + "");

                    // 获取重量段(KG)、首重、首重价格、续重、续重价格、处理费
                    String weightSection = sheetMap.get(posX + "," + j);
                    String endWeightKg = weightSection.substring(weightSection.indexOf("-") + 1).trim();
                    String endWeightG = new BigDecimal(endWeightKg).multiply(new BigDecimal("1000")).toString();
                    String startWeightKg = weightSection.substring(0, weightSection.indexOf("-")).trim();
                    // 获取首重
                    j++;
                    erpProductFreightYanwen.setFirstWeight(new BigDecimal(sheetMap.get(posX + "," + j)).multiply(new BigDecimal("1000")).toString());
                    // 获取首重价格
                    j++;
                    erpProductFreightYanwen.setFirstFreight(new BigDecimal(sheetMap.get(posX + "," + j)));
                    // 获取续重
                    j++;
                    erpProductFreightYanwen.setContinuationWeight(new BigDecimal(sheetMap.get(posX + "," + j)).multiply(new BigDecimal("1000")).toString());
                    // 获取续重价格
                    j++;
                    erpProductFreightYanwen.setContinuationFreight(new BigDecimal(sheetMap.get(posX + "," + j)));
                    // 获取处理费
                    j++;
                    erpProductFreightYanwen.setRegistrationFee(new BigDecimal(sheetMap.get(posX + "," + j)));
                    // 货品属性
                    erpProductFreightYanwen.setPackageType(0);

                    erpProductFreightYanwenList.add(erpProductFreightYanwen);
                    ii++;
                }else {
                    // 不是有效行，直接跳出
                    break loop1;
                }
            }else if(weightCalculationScheme.equals("ZONE")){
                int j = 4;
                newCoordinate = "" + posX + "," + j + "";
                // 根据具体数字判断是否是有效行
                if(StringUtils.isNumber(sheetMap.get(newCoordinate))) {
                    // 有效行
                    erpProductFreightYanwen = new ErpProductFreightYanwen();
                    // 赋值国家基本信息
                    erpProductFreightYanwen.setFreightType(freightType);
                    erpProductFreightYanwen.setCountry(sheetMap.get(posX + ",2"));
                    // 燕文仅有澳大利亚
                    erpProductFreightYanwen.setCountryCode("AU");
                    // 获取分区
                    erpProductFreightYanwen.setZone(sheetMap.get(posX + ",3").trim());
                    // 升级物流模版版本
                    erpProductFreightYanwen.setRevision((Integer.parseInt(version) + 1) + "");

                    // 获取公斤运费(元/KG)、处理费(元/件)、重量段(KG)、最小计费重量(KG)
                    // 获取公斤运费(元/KG)
                    String kgFreight = sheetMap.get(posX + "," + j);
                    // 获取处理费(元/件)
                    j++;
                    BigDecimal registrationFee = new BigDecimal(sheetMap.get(posX + "," + j));
                    // 获取重量段(KG)0.001 - 2
                    j++;
                    String weightSection = sheetMap.get(posX + "," + j);
                    String endWeightKg = weightSection.substring(weightSection.indexOf("-") + 1).trim();
                    String endWeightG = new BigDecimal(endWeightKg).multiply(new BigDecimal("1000")).toString();
                    String startWeightKg = weightSection.substring(0, weightSection.indexOf("-")).trim();
                    // 获取最小计费重量(KG)
                    j++;
                    String minimumWeightKg = sheetMap.get(posX + "," + j);
                    if (new BigDecimal(startWeightKg).compareTo(new BigDecimal(minimumWeightKg)) < 0) {
                        // 首重，可为空
                        erpProductFreightYanwen.setFirstWeight(new BigDecimal(minimumWeightKg).multiply(new BigDecimal("1000")).toString());
                    }
                    erpProductFreightYanwen.setWeight(endWeightG);
                    erpProductFreightYanwen.setFreight(new BigDecimal(kgFreight));
                    erpProductFreightYanwen.setRegistrationFee(registrationFee);
                    // 货品属性
                    erpProductFreightYanwen.setPackageType(0);

                    erpProductFreightYanwenList.add(erpProductFreightYanwen);
                    ii++;
                }else {
                    // 不是有效行，直接跳出
                    break loop1;
                }
            }
        }
        return yanwenMapper.insertBatch(erpProductFreightYanwenList) ? ii : 0;
    }


    /**
     * 更新菜鸟大包专线
     *
     * @param sheetMap
     * @return
     */
    private Boolean updateCainiaoDaBaoZhuanXian(Map<String, String> sheetMap, String reversion) {

        // 计费标准Map
        Map<String, String> feeMap = new HashMap<>();
        // 将计费标准单独拆分出来
        for (String key : sheetMap.keySet()) {
            if (sheetMap.get(key).contains("配送服务费") || sheetMap.get(key).contains("挂号服务费")
                    || sheetMap.get(key).contains("限重") || sheetMap.get(key).contains("/KG")
                    || sheetMap.get(key).contains("包裹") || sheetMap.get(key).contains("首重")
                    || sheetMap.get(key).contains("续重") || sheetMap.get(key).contains("限重")) {
                feeMap.put(key, sheetMap.get(key));
            }
        }

        // 遍历计费标准
        for (String key : feeMap.keySet()) {
            // 第一个配送服务费
            if (key.contains(",4") && feeMap.get(key).contains("配送服务费")) {
                // 获取key，转换坐标，如(16,4)，并根据坐标获取具体数据
                insertProductFreightByCndbMap(key, sheetMap, "菜鸟大包专线",
                        (reversion == null || reversion.trim().equals("")) ? "1" : reversion);
            }
        }
        return true;
    }


    /**
     * 根据读取的Excel信息，将菜鸟大包专线运费插入数据库
     *
     * @param coordinate
     * @param sheetMap
     * @param freightType
     * @param version
     * @return
     */
    private int insertProductFreightByCndbMap(String coordinate, Map<String, String> sheetMap, String freightType,
                                              String version) {
//        ErpProductFreight erpProductFreight = null;
//        List<ErpProductFreight> erpProductFreightList = new ArrayList<>();

        ErpProductFreightBale erpProductFreightBale = null;
        List<ErpProductFreightBale> erpProductFreightBaleList = new ArrayList<>();
        /**
         * 根据配送服务费，处理坐标，并获取数据坐标，如(15,4)
         * 1、数据坐标是(17,1),(17,2),(17,3)...(17,1),(17,2),(17,3)...直至为空 2、重量范围坐标为(16,4)
         * 3、计费标准坐标为(15,4)
         */
        // 根据坐标物，获取其他坐标
        String positionY = coordinate.substring(0, coordinate.indexOf(","));

        // Y坐标遍历
        int posY = Integer.parseInt(positionY) + 1;
        // 新坐标
        String newCoordinate = "";
        // 自增标记
        int ii = 0;

        // 获取计费标准
        String charging = sheetMap.get(coordinate);
        // 获取首重
        String firstWeight = "100";
        if (charging.contains("首重")) {
            String firstWeightStr = charging.substring(charging.indexOf("首重"));
            firstWeight = firstWeightStr.substring(firstWeightStr.indexOf("首重") + 2, firstWeightStr.indexOf("KG"))
                    + "000";
        }
        // 获取续重
        String continuationWeight = "100";
        if (charging.contains("每") && charging.contains("kg计重")) {
            continuationWeight = charging.substring(charging.indexOf("每") + 1, charging.indexOf("kg计重")) + "000";
        } else if (charging.contains("每") && charging.contains("g计重")) {
            continuationWeight = charging.substring(charging.indexOf("每") + 1, charging.indexOf("g计重"));
        }
        // 获取限重
        String limitWeight = "20000";
        if (charging.contains("限重")) {
            String limitWeightStr = charging.substring(charging.indexOf("限重"));
            if (limitWeightStr.contains("KG")) {
                limitWeight = limitWeightStr.substring(limitWeightStr.indexOf("限重") + 2, limitWeightStr.indexOf("KG"))
                        + "000";
            } else if (limitWeightStr.contains("g")) {
                limitWeight = limitWeightStr.substring(limitWeightStr.indexOf("限重") + 2, limitWeightStr.indexOf("g"));
            }
        }

        // 数据坐标读取
        loop1: while (true) {
            posY += 1;
            newCoordinate = "" + posY + ",4" + "";
            // 根据具体数字判断是否是有效行
            if (StringUtils.isNumber(sheetMap.get(newCoordinate))) {

                // 判断是否为有效列
                loop2: for (int j = 4; j < 20; j++) {
                    if (StringUtils.isNumber(sheetMap.get(posY + "," + j))) {
                        // 有效行
                        erpProductFreightBale = new ErpProductFreightBale();
                        // 赋值国家基本信息
                        erpProductFreightBale.setFreightType(freightType);
                        erpProductFreightBale.setCountry(sheetMap.get(posY + ",1"));
                        erpProductFreightBale.setCountryEn(sheetMap.get(posY + ",2"));
                        erpProductFreightBale.setCountryCode(sheetMap.get(posY + ",3"));

                        // 升级物流模版版本
                        erpProductFreightBale.setRevision((Integer.parseInt(version) + 1) + "");
                        // 是否计泡
                        erpProductFreightBale.setBubbleType(charging.contains("计泡") ? 1 : 0);
                        // 获取限重
                        erpProductFreightBale.setLimitWeight(limitWeight);
                        // 大包
                        erpProductFreightBale.setPackageType(2);

                        // 获取首重
                        erpProductFreightBale.setFirstWeight(firstWeight);
                        erpProductFreightBale.setFirstFreight(new BigDecimal(sheetMap.get(posY + "," + j))
                                .multiply(new BigDecimal(firstWeight)).divide(new BigDecimal("1000")));

                        // 获取续重
                        erpProductFreightBale.setContinuationWeight(continuationWeight);
                        erpProductFreightBale.setContinuationFreight(new BigDecimal(sheetMap.get(posY + "," + j))
                                .multiply(new BigDecimal(continuationWeight)).divide(new BigDecimal("1000")));
                        j++;
                        // 获取挂号费
                        erpProductFreightBale.setRegistrationFee(new BigDecimal(sheetMap.get(posY + "," + j)));

                        erpProductFreightBaleList.add(erpProductFreightBale);
                    } else {
                        // 不是有效列，直接跳出
                        break loop2;
                    }
                }
                ii++;
            } else {
                // 不是有效行，直接跳出
                break loop1;
            }

        }
        return baleMapper.insertBatch(erpProductFreightBaleList) ? ii : 0;
    }


    /**
     * 更新菜鸟特货专线-标准
     * @param sheetMap
     * @return
     */
    private Boolean updateCainiaoTeHuoZhuanXian(Map<String, String> sheetMap, String reversion){
        // 计费标准Map
        Map<String, String> feeMap = new HashMap<>();
        // 将计费标准单独拆分出来
        for (String key : sheetMap.keySet()){
            if(sheetMap.get(key).contains("配送服务费") || sheetMap.get(key).contains("挂号服务费")
                    || sheetMap.get(key).contains("限重") || sheetMap.get(key).contains("/KG")
                    || sheetMap.get(key).contains("包裹") || sheetMap.get(key).contains("首重")
                    || sheetMap.get(key).contains("续重") || sheetMap.get(key).contains("限重")){
                feeMap.put(key, sheetMap.get(key));
            }
        }

        // 遍历计费标准
        for (String key : feeMap.keySet()) {
            // 第一个配送服务费
            if (key.contains(",4") && feeMap.get(key).contains("配送服务费")) {
                // 获取key，转换坐标，如(16,4)，并根据坐标获取具体数据
                insertProductFreightByCnthMap(key, sheetMap, "菜鸟特货专线-标准",
                        (reversion == null || reversion.trim().equals("")) ? "1" : reversion);
            }
        }
        return true;
    }

    /**
     * 根据读取的Excel信息，将菜鸟特货专线-标准运费插入数据库
     * @param coordinate
     * @param sheetMap
     * @param freightType
     * @param version
     * @return
     */
    private int insertProductFreightByCnthMap(String coordinate, Map<String, String> sheetMap, String freightType, String version){
        ErpProductFreight erpProductFreight = null;
        List<ErpProductFreight> erpProductFreightList = new ArrayList<>();
        /**
         * 根据配送服务费，处理坐标，并获取数据坐标，如(15,4)
         * 1、数据坐标是(17,1),(17,2),(17,3)...(17,1),(17,2),(17,3)...直至为空
         * 2、重量范围坐标为(16,4)
         */
        // 根据坐标物，获取其他坐标
        String positionY = coordinate.substring(0, coordinate.indexOf(","));

        // Y坐标遍历
        int posY = Integer.parseInt(positionY) + 1;
        // 新坐标
        String newCoordinate = "";
        // 自增标记
        int ii = 0;

        // 数据坐标读取
        loop1: while (true) {
            posY += 1;
            newCoordinate = "" + posY + ",4" + "";
            // 根据具体数字判断是否是有效行
            if (StringUtils.isNumber(sheetMap.get(newCoordinate))) {

                // 判断是否为有效列
                loop2: for (int j = 4; j < 20; j++) {
                    if (StringUtils.isNumber(sheetMap.get(posY + "," + j))) {
                        // 有效行
                        erpProductFreight = new ErpProductFreight();
                        // 赋值国家基本信息
                        erpProductFreight.setFreightType(freightType);
                        erpProductFreight.setCountry(sheetMap.get(posY + ",1"));
                        erpProductFreight.setCountryEn(sheetMap.get(posY + ",2"));
                        erpProductFreight.setCountryCode(sheetMap.get(posY + ",3"));

                        // 升级物流模版版本
                        erpProductFreight.setRevision((Integer.parseInt(version) + 1) + "");
                        // 特货
                        erpProductFreight.setPackageType(1);

                        // 获取重量，如：0~3000g(含)
                        String weightStr = sheetMap.get((Integer.parseInt(positionY) + 1) + ",4");
                        erpProductFreight.setWeight(weightStr.substring(weightStr.indexOf("~")+1, weightStr.indexOf("g")));
                        // 获取价格
                        erpProductFreight.setFreight(new BigDecimal(sheetMap.get(posY + "," + j)));
                        j++;
                        // 获取挂号费
                        erpProductFreight.setRegistrationFee(new BigDecimal(sheetMap.get(posY + "," + j)));

                        erpProductFreightList.add(erpProductFreight);
                    } else {
                        // 不是有效列，直接跳出
                        break loop2;
                    }
                }
                ii++;
            } else {
                // 不是有效行，直接跳出
                break loop1;
            }

        }
        return baseMapper.insertBatch(erpProductFreightList) ? ii : 0;
    }


    /**
     * 更新菜鸟经济相关物流
     * @param sheetMap
     * @return
     */
    private Boolean updateCainiaoEconomy(Map<String, String> sheetMap, String reversion, String freightType){

        // 计费标准Map
        Map<String, String> feeMap = new HashMap<>();
        // 将配送服务费这一列单独拆分出来
        for (String key : sheetMap.keySet()){
            if(sheetMap.get(key).trim().contains("配送服务费")){
                feeMap.put(key, sheetMap.get(key));
            }
        }

        // 遍历计费标准
        for (String key : feeMap.keySet()){
            // 第一个配送服务费
            if(key.contains(",4") && feeMap.get(key).contains("配送服务费")){
                // 获取key，转换坐标，如(16,4)，并根据坐标获取具体数据
                insertCaiNiaoEconomyProductFreightByMap(key, sheetMap, freightType, (reversion == null || reversion.trim().equals("")) ? "1" : reversion);
            }
        }
        return true;
    }


    /**
     * 根据读取的Excel信息，将运费插入菜鸟无忧物流经济类的数据库表
     * @param coordinate
     * @param sheetMap
     * @param freightType
     * @param version
     * @return
     */
    private int insertCaiNiaoEconomyProductFreightByMap(String coordinate, Map<String, String> sheetMap, String freightType, String version){
        ErpProductFreightEconomy erpProductFreightEconomy = null;
        List<ErpProductFreightEconomy> erpProductFreightEconomyList = new ArrayList<>();

        // 判断费用币种类型
        boolean isRMB = true;
        if(sheetMap.get(coordinate).contains("美元") || sheetMap.get(coordinate).contains("USD")){
            isRMB = false;
        }

        /**
         * 根据配送服务费坐标（比如坐标是8,4）
         * 1、获取上一行（7，4），获取重量最大值
         * 2、获取下一列（7，5），获取服务费
         * 3、数据从下一行（9，4），按列循环查询
         * 4、最终查询到最低计费重量，即首重
         */
        // 根据坐标物，获取其他坐标
        String positionX = coordinate.substring(0, coordinate.indexOf(","));
        String positionY = coordinate.substring(coordinate.indexOf(",")+1);

        // X坐标遍历
        int posX = Integer.parseInt(positionX);
        if(freightType.equals("菜鸟超级经济")){
            posX += 1;
        }
        // Y坐标遍历
        int posY = Integer.parseInt(positionY);
        // 新坐标
        String newCoordinate = "";
        // 自增标记
        int ii = 0;

        loop1 : while (true){
            posX += 1;
            newCoordinate = "" + posX + "," + 4 + "";
            // 根据具体数字判断是否是有效行
            if(StringUtils.isNumber(sheetMap.get(newCoordinate))) {
                loop2: for (int j = 4; j < 20; j++){
                    newCoordinate = "" + posX + "," + j + "";

                    // 根据具体数字判断是否是有效行
                    if(StringUtils.isNumber(sheetMap.get(newCoordinate))) {
                        // 有效行
                        erpProductFreightEconomy = new ErpProductFreightEconomy();
                        // 赋值国家基本信息
                        erpProductFreightEconomy.setFreightType(freightType);
                        erpProductFreightEconomy.setCountry(sheetMap.get(posX + ",1"));
                        erpProductFreightEconomy.setCountryEn(sheetMap.get(posX + ",2"));
                        erpProductFreightEconomy.setCountryCode(sheetMap.get(posX + ",3"));
                        // 升级物流模版版本
                        erpProductFreightEconomy.setRevision((Integer.parseInt(version) + 1) + "");

                        // 获取开始重量最大值
                        String startWeight = "1";
                        String endWeight = "10";
                        String minWeight = "1";
                        // 根据运费模版类型进行区分判断
                        if(freightType.equals("菜鸟特货专线－超级经济")){
                            if (sheetMap.containsKey((Integer.parseInt(positionX)-1) + "," + j)){
                                if(sheetMap.get((Integer.parseInt(positionX)-1) + "," + j).contains("包裹重量")){
                                    String weightSection = sheetMap.get((Integer.parseInt(positionX)-1) + "," + j);
//									System.out.println("type=" + freightType + ",zb="+sheetMap.get((Integer.parseInt(positionX)-1) + "," + j)+",con="+weightSection);

                                    endWeight = weightSection.substring(weightSection.indexOf("-")+1, weightSection.lastIndexOf("克")).trim();
                                }else {
                                    throw new RuntimeException();
                                }
                            }else {
                                // 后面没有数据了
                                break loop2;
                            }
                        }else if (freightType.equals("菜鸟超级经济")){
                            if(erpProductFreightEconomy.getCountryCode().equals("US")){
                                minWeight = "20";
                            }
                            if (sheetMap.containsKey((Integer.parseInt(positionX)+1) + "," + j)){
                                if(sheetMap.get((Integer.parseInt(positionX)+1) + "," + j).contains("包裹重量")){
                                    String weightSection = sheetMap.get((Integer.parseInt(positionX)+1) + "," + j);
                                    endWeight = weightSection.substring(weightSection.indexOf("-")+1, weightSection.lastIndexOf("克")).trim();
                                }else if (sheetMap.get((Integer.parseInt(positionX)+1) + "," + j).contains("备注")){
//									String minWeightStr = sheetMap.get((Integer.parseInt(positionX)+1) + "," + j);
//									minWeight = minWeightStr.substring(minWeightStr.indexOf("首重")+2, minWeightStr.indexOf("g")).trim();
                                }else {
                                    throw new RuntimeException();
                                }
                            }else {
                                // 后面没有数据了
                                break loop2;
                            }
                        }else {
                            if(erpProductFreightEconomy.getCountryCode().equals("US")){
                                minWeight = "20";
                            }else {
                                minWeight = "10";
                            }
                            if (sheetMap.containsKey((Integer.parseInt(positionX)-1) + "," + j)){
                                if(sheetMap.get((Integer.parseInt(positionX)-1) + "," + j).contains("包裹重量")){
                                    String weightSection = sheetMap.get((Integer.parseInt(positionX)-1) + "," + j);
//									System.out.println("type=" + freightType + ",zb="+sheetMap.get((Integer.parseInt(positionX)-1) + "," + j)+",con="+weightSection);
                                    endWeight = weightSection.substring(weightSection.indexOf("-")+1, weightSection.lastIndexOf("克")).trim();
                                    startWeight = weightSection.substring(weightSection.indexOf("包裹重量为")+5, weightSection.indexOf("-")).trim();
                                }else if (sheetMap.get((Integer.parseInt(positionX)-1) + "," + j).contains("最低计费重量")){
//									String minWeightStr = sheetMap.get((Integer.parseInt(positionX)-1) + "," + j);
//									minWeight = minWeightStr.substring(0, minWeightStr.indexOf("g")).trim();
                                }else {
                                    throw new RuntimeException();
                                }
                            }else {
                                // 后面没有数据了
                                break loop2;
                            }
                        }

                        // 获取重量
                        erpProductFreightEconomy.setWeight(endWeight);
                        // 获取运费
                        BigDecimal freight = new BigDecimal(sheetMap.get("" + posX + "," + j + ""));
                        erpProductFreightEconomy.setFreight(isRMB ? freight : freight.multiply(new BigDecimal("7.2")));

                        // 判断是否存在首重
                        if(new BigDecimal(startWeight).compareTo(new BigDecimal(minWeight)) <= 0){
                            erpProductFreightEconomy.setFirstWeight(minWeight);
                        }

                        // 获取服务费
                        j++;
                        BigDecimal registrationFee = new BigDecimal(sheetMap.get("" + posX + "," + j + ""));
                        erpProductFreightEconomy.setRegistrationFee(isRMB ? registrationFee : registrationFee.multiply(new BigDecimal("7.2")));

                        erpProductFreightEconomyList.add(erpProductFreightEconomy);
                        ii++;
                    }else {
                        // 不是有效列，直接跳出
                        break loop2;
                    }
                }
            }else {
                break loop1;
            }
        }
        return economyMapper.insertBatch(erpProductFreightEconomyList) ? ii : 0;
    }
}
